from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from dateutil import parser

from QuickTools import tools_pmc


def detect_date(text):
    try:
        date = parser.parse(text)
        return date.strftime('%Y/%m/%d')
    except:
        return None



def title(str_1):
    list_str1 = ["序号", "日期", "版本", "变更项", "配置说明", "料号"]
    if str_1 is not None:
        for str_2 in list_str1:
            if str_2 in str(str_1):
                return True
    return False


wb = load_workbook("//192.168.70.101/19计划管理部/01.计划/5，物控管理/8、相机版本变更分析/" + tools_pmc.today_str1 + ".xlsx")
wb.create_sheet("原数据-汇总")
wb.create_sheet("处理后数据")
wb.create_sheet("弃用行信息")
ws1 = wb["原数据-汇总"]
ws2 = wb["处理后数据"]
ws3 = wb["弃用行信息"]

max_col = 17
a = 2
a1 = 1

temp_date = 1
temp_sn = 1
temp_bby = 1
temp_bbh = 1
temp_pz = 1
temp_bgyy = 1
temp_sxsm = 1

for i in range(1, ws1.max_row + 1):
    print(i)
    int_2 = 0  # 表头行确定参数2
    # int_3 = 0  # 无效行判定
    # for j in range(2, max_col):
    #     if ws1.cell(i, j).value is None:
    #         int_3 += 1
    # if int_3 > 5:
    #     ws3.cell(a1, 1).value = i
    #     a1 += 1
    #     continue
    for j in range(2, max_col):
        if title(ws1.cell(i, j).value):
            int_2 += 1
    if int_2 > 3:
        for j in range(2, max_col):
            if ws1.cell(i, j).value is not None and "料号" in str(ws1.cell(i, j).value):
                temp_sn = j
        for j in range(2, max_col):
            if ws1.cell(i, j).value is not None and "日期" in str(ws1.cell(i, j).value):
                temp_date = j
        for j in range(2, max_col):
            if ws1.cell(i, j).value is not None and "原版本" in str(ws1.cell(i, j).value):
                temp_bby = j
        for j in range(2, max_col):
            if ws1.cell(i, j).value is not None and "变更后版本" in str(ws1.cell(i, j).value):
                temp_bbh = j
        for j in range(2, max_col):
            if ws1.cell(i, j).value is not None and "配置" in str(ws1.cell(i, j).value):
                temp_pz = j
        for j in range(2, max_col):
            if ws1.cell(i, j).value is not None and "变更原因" in str(ws1.cell(i, j).value):
                temp_bgyy = j
        for j in range(2, max_col):
            if ws1.cell(i, j).value is not None and "生效说明" in str(ws1.cell(i, j).value):
                temp_sxsm = j

        continue
    if ws1.cell(i, temp_sn).value is None:
        ws3.cell(a1, 1).value = i
        a1 += 1
        continue
    if ws1.cell(i, temp_sn).value[0] != "3" or ws1.cell(i, temp_sn).value == ws1.cell(i - 1, temp_sn).value:
        ws3.cell(a1, 1).value = i
        a1 += 1
        continue

    ws2.cell(a, 2).value = ws1.cell(i, temp_sn).value  # 品号
    ws2.cell(a, 1).value = ws1.cell(i, 1).value[ws1.cell(i, 1).value.find("]") + 1:]  # 规格
    ws2.cell(a, 3).value = detect_date(ws1.cell(i, temp_date).value)
    ws2.cell(a, 4).value = ws1.cell(i, temp_bby).value
    ws2.cell(a, 5).value = ws1.cell(i, temp_bbh).value
    ws2.cell(a, 6).value = ws1.cell(i, temp_pz).value
    ws2.cell(a, 7).value = ws1.cell(i, temp_bgyy).value
    ws2.cell(a, 8).value = ws1.cell(i, temp_sxsm).value
    ws2.cell(a, 9).value = temp_date
    ws2.cell(a, 10).value = i
    a += 1

list_title = ["规格", "品号", "建号日期", "原版本", "变更后版本", "配置说明", "变更原因", "生效说明"]
list_leng = [18, 15, 12, 12, 12, 50, 65, 40]
for i in range(1, 9):
    ws2.cell(1, i).value = list_title[i - 1]
    ws2.column_dimensions[get_column_letter(i)].width = list_leng[i - 1]

wb.save("//192.168.70.101/19计划管理部/01.计划/5，物控管理/8、相机版本变更分析/" + tools_pmc.today_str1 + "-1.xlsx")
